"""
检查数据库中的策略和回测结果
"""
import sqlite3
import pandas as pd

def check_database():
    """检查数据库内容"""
    conn = sqlite3.connect('trading_backtest.db')
    
    print("=" * 80)
    print("数据库统计信息")
    print("=" * 80)
    
    # 1. 统计策略数量
    query = '''
        SELECT strategy_name, COUNT(*) as count, 
               AVG(total_return) as avg_return,
               MAX(total_return) as max_return,
               MIN(total_return) as min_return
        FROM backtest_results
        GROUP BY strategy_name
        ORDER BY count DESC
    '''
    df = pd.read_sql_query(query, conn)
    
    print("\n策略统计：")
    print("-" * 80)
    if not df.empty:
        for _, row in df.iterrows():
            print(f"策略名称: {row['strategy_name']}")
            print(f"  回测次数: {row['count']}")
            print(f"  平均收益率: {row['avg_return']*100:.2f}%")
            print(f"  最高收益率: {row['max_return']*100:.2f}%")
            print(f"  最低收益率: {row['min_return']*100:.2f}%")
            print()
    else:
        print("  暂无数据")
    
    # 2. 最近的回测记录
    query = '''
        SELECT id, strategy_name, symbol, start_date, end_date,
               total_return, sharpe_ratio, max_drawdown, created_at
        FROM backtest_results
        ORDER BY created_at DESC
        LIMIT 10
    '''
    df = pd.read_sql_query(query, conn)
    
    print("\n最近10次回测：")
    print("-" * 80)
    if not df.empty:
        for _, row in df.iterrows():
            print(f"ID: {row['id']} | {row['strategy_name']} | {row['symbol']} | "
                  f"收益率: {row['total_return']*100:.2f}% | "
                  f"夏普率: {row['sharpe_ratio']:.2f} | "
                  f"时间: {row['created_at']}")
    else:
        print("  暂无数据")
    
    # 3. 表结构信息
    print("\n\n数据库表信息：")
    print("-" * 80)
    cursor = conn.cursor()
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
    tables = cursor.fetchall()
    
    for table in tables:
        table_name = table[0]
        cursor.execute(f"SELECT COUNT(*) FROM {table_name}")
        count = cursor.fetchone()[0]
        print(f"表名: {table_name:30s} | 记录数: {count}")
    
    conn.close()
    
    print("\n" + "=" * 80)


if __name__ == '__main__':
    check_database()
